package ru.fadeev.crammer.data;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Vector;

import ru.fadeev.crammer.DatabaseManager;
import ru.fadeev.crammer.GFIndicesStorage;
import ru.fadeev.crammer.IndexerWordTask;
import ru.fadeev.crammer.Settings;
import ru.fadeev.crammer.TestManager;
import ru.fadeev.crammer.Utils;


/**
 * Sheet class represents one of the data entities of Crammer program.
 * Sheet consists of Words. Sheets can conflate in Dictionary.  
 * @author Konstantin Fadeyev
 *
 */
public class Sheet implements WordsDataListener {
	// DB related fields
	private int id;
	private String name;
	private Vector<WordRecord> wordRecords; 
    
    // non-DB related fields
	private int[] testWordsCount;
	private boolean toTest;
	
	/**
	 * Array that contains information of how many words don't have information about
	 * they should or should be tested (how many words is not 'checked')
	 */
	private int[] uncheckedWordsCount;
	
	private int wordsCount;   
	private WordsDataListener wordsDataListener;
    
    
	
    /**
	 * Constructs Sheet with default initial values
	 */
	public Sheet() {
		id = -1;
		name = "";
		wordRecords = null;
		
		wordsCount = 0;		
		testWordsCount = new int[TestManager.TESTS_NUMBER];
		uncheckedWordsCount = new int[TestManager.TESTS_NUMBER];
		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
			testWordsCount[i] = 0;
			uncheckedWordsCount[i] = 0;
		}
		
		toTest = false;		
		wordsDataListener = null;
	}


	/**
	 * Adds word to Sheet without saving to database. Used by internal
	 * functions and while importing from file.
	 * @param word word added
	 */
	void addWordRecord(WordRecord word) {
		if(wordRecords == null) {
			wordRecords = new Vector<WordRecord>();
		}
		wordRecords.addElement(word);
		word.setWordsDataListener(this);
	}
	
	
	/**
	 * Empty implementation of WordsDataListener interface function
	 */
	public void elementInserted(int index) {}
	
	
	/**
	 * Returns database table id of sheet
	 * @return database table id of sheet
	 */
	public int getId() {
		return id;
	}
	
	
	/**
	 * Returns sheet name
	 * @return sheet name
	 */
	public String getName() {
		return name;
	}

	
	/**
	 * Returns number of words in sheet that need to be tested
	 * @param testType one of TestManager test types
	 * @return number of words in sheet that need to be tested
	 */
	public int getTestWordsCount(int testType) {
		return testWordsCount[testType];
	}

	
	/**
	 * Returns number of words in sheet that haven't been checked for test needs yet.
	 * When Crammer starts, all words are unchecked. Then process of determining 
	 * words test needs (TestNeedsWatcher) starts.
	 * @param testType one of TestManager test types.
	 * @return number of words in sheet that haven't been checked yet
	 */
	int getUncheckedWordsCount(int testType) {
		return uncheckedWordsCount[testType];
	}
	
	
	/**
	 * Returns word for this sheet
	 * @param index index of word in sheet
	 * @return object of Word
	 */
	public WordRecord getWordRecord(int index) {
		if(wordRecords == null) {
			loadWordRecords();
		}
		return (WordRecord)wordRecords.elementAt(index);
	}
	
	
	/**
	 * Returns total number of words in sheet. Function doesn't load words
	 * from database, only words number.
	 * @return total number of words in sheet
	 */
	public int getWordsCount() {
		return wordsCount;
	}
	
	
	/**
     * Inserts words into database
     * Used while importing words data from file
     * @param storage object of GFIndicesStorage
     */
    void insertWordRecordsToDB(GFIndicesStorage storage) {
    	if(wordRecords == null) {
    		wordRecords = new Vector<WordRecord>();
    	}
		try {
			Vector<String> wordsForBank = new Vector<String>(); 
			boolean loadFromBank = 
				Settings.getBoolean("loadPhrasesFromBankForNewWords");
	    	Connection connection = DatabaseManager.getConnection();  
	    	PreparedStatement insertStatement = connection.prepareStatement(
	    			"INSERT INTO WORD_RECORD " +
					"(SHEET_ID, " +
						"WORD, " +
						"TRANSCRIPTION, " +
						"TRANSLATION, " +
						"PROMPT, " +
						"CREATE_DATE, " +
						"ORDER_NUMBER) " +
					"VALUES (" + id + ", ?, ?, ?, ?, ?, ?)");

	    	CallableStatement identityStatement = 
	    		connection.prepareCall("{?= CALL IDENTITY()}");
	    	for(int i = 0; i < size(); i++) {
	    		WordRecord wordRecord = (WordRecord)wordRecords.elementAt(i);
				insertStatement.setString(1, wordRecord.getWord());
				insertStatement.setString(2, wordRecord.getTranscription());
				insertStatement.setString(3, wordRecord.getTranslation());
				insertStatement.setString(4, wordRecord.getPrompt());
				insertStatement.setTimestamp(5, new Timestamp(
						wordRecord.getCreateDate()));
				insertStatement.setInt(6, i);
				insertStatement.executeUpdate();
				
				ResultSet resultSet = identityStatement.executeQuery();	
				resultSet.next();
				int id = resultSet.getInt(1);
				wordRecord.setId(id);
				wordRecord.insertHistoryToDB();

				IndexerWordTask wordTask = 
					new IndexerWordTask(wordRecord, true);
				storage.addGFIndexerTask(wordTask);
				if(loadFromBank) {
					wordsForBank.addElement(wordRecord.getWord());
				}
			}
	    	
	    	identityStatement.close();
			insertStatement.close();
			connection.close();
			
			if(loadFromBank && (wordsForBank.size() != 0)) {				
				storage.addPHBTask(wordsForBank);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
    }
	
    
    /**
     * Returns true if every word in sheet was checked for test needs.When Crammer
     * starts all words are unchecked. Then process of determining 
	 * words test needs (TestNeedsWatcher) starts.
     * @param testType one of TestManager test types
     * @return true if every word in sheet was checked
     */
	public boolean isChecked(int testType){
		return uncheckedWordsCount[testType] == 0;
	}
	
	
	/**
	 * Returns true if Sheet was marked as chosen to test
	 * @return true if Sheet was marked as chosen to test
	 */
	public boolean isToTest() {
		return toTest;
	}
	

	/**
	 * Loads words from database. Used only by internal functions. Words are 
     * loaded only at the moment when some outer object asks for them, 
     * not any time sooner.
	 */
	private void loadWordRecords() {
		wordRecords = new Vector<WordRecord>();
		try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();;
			ResultSet resultSet = statement.executeQuery(
        			"SELECT ID, WORD, TRANSCRIPTION, " +
        				"TRANSLATION, PROMPT, CREATE_DATE " +
        			"FROM WORD_RECORD " +
        			"WHERE SHEET_ID = " + id +
        			" ORDER BY ORDER_NUMBER"
        			);	
        	while (resultSet.next()) {
        		WordRecord newWordRecord = new WordRecord();
        		newWordRecord.setId(resultSet.getInt(1));
        		newWordRecord.setWord(resultSet.getString(2));
        		newWordRecord.setTranscription(resultSet.getString(3));
        		newWordRecord.setTranslation(resultSet.getString(4));
        		newWordRecord.setPrompt(resultSet.getString(5));       		
        		Timestamp createDate = resultSet.getTimestamp(6);
        		newWordRecord.setCreateDate(createDate.getTime());
        		addWordRecord(newWordRecord);
        	}
        	statement.close();
        	connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }		
    }
	
	
	/**
	 * Removes all words and related information from database
     * Used by dictionary removing functions
	 * @param storage object of GFIndicesStorage
	 */
    void removeAllWordRecordsFromDB(GFIndicesStorage storage) {
    	try {
	    	Connection connection = DatabaseManager.getConnection();  
	    	PreparedStatement historyStatement = connection.prepareStatement(
	    			"DELETE FROM TEST_HISTORY " +
	    			"WHERE WORD_ID = ?");
	    	PreparedStatement deleteStatement = connection.prepareStatement(
	    			"DELETE FROM WORD_RECORD " +
	    			"WHERE ID = ?");
	    	synchronized(storage) {
				for(int i = 0; i < size(); i++) {
					WordRecord wordRecord = (WordRecord)wordRecords.elementAt(i);	
					storage.deleteGFIndices(wordRecord.getId(), false, connection);
					historyStatement.setInt(1, wordRecord.getId());
					historyStatement.addBatch();		
					deleteStatement.setInt(1, wordRecord.getId());
					deleteStatement.addBatch();					
				}
				historyStatement.executeBatch();
				historyStatement.close();
				deleteStatement.executeBatch();
				deleteStatement.close();
	    	}
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
    	
    }
	
    
	/**
	 * Save words to database depending on content of WordEditList 
	 * (filled during editing sheet in AddEditSheetDialog). 
	 * Unlike sheets or dictionaries words are added to sheet, removed 
	 * from sheet and saved to DB alltoghether by one big list 'editList', 
	 * keeping information about what to do with each word 
	 * (INSERT, UPDATE or DELETE)
	 * @param editList object containing information about changes 
	 * in words list
	 * @param storage object of GFIndicesStorage
	 * @param dictionaryIndex index of dictionary containing Sheet
	 * @param sheetIndex index of this sheet in dictionary
	 */
    public synchronized void saveWordRecords(WordEditList editList, 
    		GFIndicesStorage storage, int dictionaryIndex, int sheetIndex) {
		try {
			Vector<String> wordsForBank = new Vector<String>(); 
			boolean loadFromBank = Settings.getBoolean("loadPhrasesFromBankForNewWords");

	    	Connection connection = DatabaseManager.getConnection();  
	    	PreparedStatement insertStatement = connection.prepareStatement(
	    			"INSERT INTO WORD_RECORD " +
					"(SHEET_ID, " +
						"WORD, " +
						"TRANSCRIPTION, " +
						"TRANSLATION, " +
						"PROMPT, " +
						"CREATE_DATE, " +
						"ORDER_NUMBER) " +
					"VALUES (" + id + ", ?, ?, ?, ?, ?, ?)");

	    	PreparedStatement updateStatement = connection.prepareStatement(
	    			"UPDATE WORD_RECORD " +
	    			"SET WORD = ?, " +
						"TRANSCRIPTION = ?, " +
	    				"TRANSLATION = ?, " +
	    				"PROMPT = ?, " +
	    				"CREATE_DATE = ?, " +
	    				"ORDER_NUMBER = ? " +
	    			"WHERE ID = ?");
	    	
	    	PreparedStatement updateOrderStatement = 
	    		connection.prepareStatement(
	    			"UPDATE WORD_RECORD " +
	    			"SET ORDER_NUMBER = ? " +
	    			"WHERE ID = ?");
	    	
	    	PreparedStatement historyStatement = connection.prepareStatement(
	    			"DELETE FROM TEST_HISTORY " +
	    			"WHERE WORD_ID = ?");
	    	PreparedStatement deleteStatement = connection.prepareStatement(
	    			"DELETE FROM WORD_RECORD " +
	    			"WHERE ID = ?");
	    	
	    	CallableStatement identityStatement = 
	    		connection.prepareCall("{?= CALL IDENTITY()}");
	    	
	    	Vector<WordRecord> newWordRecords = new Vector<WordRecord>();
	    	synchronized(storage) {
		    	for(int i = 0; i < editList.size(); i++) {
		    		WordEditListRecord editListrecord = editList.getRecord(i);
		    		WordRecord editListWordRecord = 
		    			editListrecord.getWordRecord();
		    		WordRecord originalWordRecord;
					if(editListrecord.status == WordEditListRecord.INSERTED) {	
						editListrecord.getWordRecord().setCreateDate(
								System.currentTimeMillis());
						insertStatement.setString(1, 
								editListWordRecord.getWord());
						insertStatement.setString(2, 
								editListWordRecord.getTranscription());
						insertStatement.setString(3, 
								editListWordRecord.getTranslation());
						insertStatement.setString(4, 
								editListWordRecord.getPrompt());
						insertStatement.setTimestamp(5, new Timestamp(
								editListWordRecord.getCreateDate()));
						insertStatement.setInt(6, i);
						insertStatement.executeUpdate();
						
						ResultSet resultSet = identityStatement.executeQuery();	
						resultSet.next();
						int id = resultSet.getInt(1);
						editListWordRecord.setId(id);
						newWordRecords.addElement(editListWordRecord);
						editListWordRecord.setWordsDataListener(this);
		        		for(int j = 0; j < TestManager.TESTS_NUMBER; j++) {
		        			uncheckedWordsCount[j]++;
		        		}
		        		IndexerWordTask wordTask = new IndexerWordTask(
		        				editListWordRecord, true);
						storage.addGFIndexerTask(wordTask);
						if(loadFromBank) {
							wordsForBank.addElement(
									editListWordRecord.getWord());
						}
					} else if (editListrecord.status ==  WordEditListRecord.UPDATED) {	
						updateStatement.setString(1, editListWordRecord.getWord());
						updateStatement.setString(2, editListWordRecord.getTranscription());
						updateStatement.setString(3, editListWordRecord.getTranslation());
						updateStatement.setString(4, editListWordRecord.getPrompt());
						updateStatement.setTimestamp(5, new Timestamp(
								editListWordRecord.getCreateDate()));
						updateStatement.setInt(6, i);
						updateStatement.setInt(7, 
								editListWordRecord.getId());
						updateStatement.addBatch();					
						IndexerWordTask wordTask = new IndexerWordTask(
								editListWordRecord, false);
						storage.addGFIndexerTask(wordTask);
						originalWordRecord = 
							getWordRecord(editListrecord.getIndex());
						originalWordRecord.copy(editListWordRecord);
						newWordRecords.addElement(originalWordRecord);
						originalWordRecord.setWordsDataListener(this);
						if(loadFromBank) {
							wordsForBank.addElement(editListWordRecord.getWord());
						}
					} else if (editListrecord.status == WordEditListRecord.DELETED) {	
						storage.deleteGFIndices(editListWordRecord.getId(), false, connection);	
						historyStatement.setInt(1, editListWordRecord.getId());
						historyStatement.addBatch();		
						deleteStatement.setInt(1, editListWordRecord.getId());
						deleteStatement.addBatch();	
						for(int j = 0; j<TestManager.TESTS_NUMBER; j++) {
							if(!editListWordRecord.isChecked(j)) {
								testWordsCountChanged(0, j, true, 
										dictionaryIndex, sheetIndex);
							} else {
								testWordsCountChanged(
										editListWordRecord.isNeededToTest(j) ? -1 : 0, 
										j, true, dictionaryIndex, sheetIndex);
							}
						}	
					} else {
						updateOrderStatement.setInt(1, i);
						updateOrderStatement.setInt(2,
								editListWordRecord.getId());
						updateOrderStatement.addBatch();
						
						originalWordRecord = getWordRecord(editListrecord.getIndex());
						newWordRecords.addElement(originalWordRecord);
						originalWordRecord.setWordsDataListener(this);
					}
				}
		    	wordRecords = newWordRecords;
		    	setWordsCount(newWordRecords.size());
		    	
		    	historyStatement.executeBatch();
				historyStatement.close();
				deleteStatement.executeBatch();
				deleteStatement.close();
		    	identityStatement.close();
				insertStatement.close();
				updateStatement.executeBatch();
				updateStatement.close();
				updateOrderStatement.executeBatch();
				updateOrderStatement.close();
				if(loadFromBank && (wordsForBank.size() != 0)) {
					storage.addPHBTask(wordsForBank);
				}
	    	}
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
    }
	
    
    /**
     * Sets database table id for sheet
     * @param value new database table id
     */
	void setId(int value) {
		id = value;
	}
	

	/**
	 * Sets new sheet name
	 * @param n new sheet name
	 */
	public void setName(String n) {
		name = n;
	}

	
	/**
	 * Marks this sheet as chosen to test (or not).
	 * @param value true if this sheet will be chosen to test
	 */
    public void setToTest(boolean value) {
		toTest = value;
	}


    /**
     * Sets number of words in sheet that haven't been checked for test needs yet.
	 * When Crammer starts, all words are unchecked. Then process of determining 
	 * words test needs (TestNeedsWatcher) starts.
     * @param value new number of words in sheet that haven't been checked yet
     * @param testType one of TestManager test types
     */
    void setUncheckedWordsCount(int value, int testType) {
		uncheckedWordsCount[testType] = value;
	}

    
    /**
     * Sets total number of words in sheet
     * @param value new total number of words in sheet
     */
    void setWordsCount(int value) {
		int change = value - wordsCount;
		wordsCount = value;
		if(wordsDataListener != null) {
			wordsDataListener.wordsCountChanged(change);
		}
	}
    
    /**
     * Sets new WordsDataListener
     * @param listener new WordsDataListener
     */
    public void setWordsDataListener(WordsDataListener listener) {
		wordsDataListener = listener;
	}

    
    /**
     * Returns total number of words in sheet. If words haven't been loaded
     * from database function loads them.
     * @return total number of words in sheet
     */
    public int size() {
		if(wordRecords == null) {
			loadWordRecords();
		}
		return wordRecords.size();
	}
	
    
    /**
     * Implements WordsDataListener interface testWordsCountChanged function
     */
    public void testWordsCountChanged(int change, int testType, 
    		boolean unchecked, int dictionaryIndex, int sheetIndex) {
    	
    	testWordsCount[testType] += change;
    	if(unchecked) {
    		uncheckedWordsCount[testType] -= 1;
    	}
    	if(wordsDataListener != null) {
    		wordsDataListener.testWordsCountChanged(change, testType, 
					unchecked, dictionaryIndex, sheetIndex);
		}
    }
    
    
    /**
     * This function was redefined so it returns sheet name. Used for simpler
     * showing it in table.
     */
    public String toString() {
    	return name;
    }
    
    
    /**
     * Replaces word at 'index' by 'w' and saves changes to database. Affects
     * only word text, transcription, translation and prompt. Doesn't change 
     * test history
     * @param w new word
     * @param index index of replaced word in sheet
     * @param storage object of GFIndicesStorage
     */
	public void updateWordRecord(WordRecord w, 
			int index, GFIndicesStorage storage) {
    	WordRecord wordRecordForUpdate = wordRecords.elementAt(index);
    	wordRecordForUpdate.copy(w);
    	try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = 
				connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
			statement.executeUpdate("UPDATE WORD_RECORD " +
					"SET WORD = '" + Utils.prep(w.getWord(), 
							Settings.WORD_MAX_LENGTH) + "'," +
						"TRANSCRIPTION = '" + Utils.prep(
								w.getTranscription(), 
								Settings.TRANSCRIPTION_MAX_LENGTH) + "'," +
						"TRANSLATION = '" + Utils.prep(
								w.getTranslation(), 
								Settings.TRANSLATION_MAX_LENGTH) + "'," +
						"PROMPT = '" + Utils.prep(w.getPrompt(), 
								Settings.PROMPT_MAX_LENGTH) + "'" +
					"WHERE ID = " + w.getId());
	    	statement.close();
	    	IndexerWordTask wordTask = new IndexerWordTask(wordRecordForUpdate, 
	    			false);
			storage.addGFIndexerTask(wordTask);
	    	connection.close();	
	    	
			Vector<String> wordVector = new Vector<String>();
    		wordVector.addElement(w.getWord());
    		storage.addPHBTask(wordVector);
    	} catch (Exception e) {
	        e.printStackTrace();
	    }	
	}
	
	/**
	 * Empty implementation of WordsDataListener interface function
	 */
	public void wordsCountChanged(int change) {}
}
